package com.ctshk.rpc.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ctshk.rpc.system.dto.SysMenuButtonDTO;
import com.ctshk.rpc.system.entity.SysMenu;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 菜单表 Mapper 接口
 * </p>
 *
 * @author 谢诗宏
 * @since 2020-12-23
 */
public interface SysMenuMapper extends BaseMapper<SysMenu> {

    /**
     * 获取所有一级节点,不过滤权限
     * @param sysType
     * @return
     */
    @Select("SELECT tsm.* FROM t_sys_menu tsm " +
            "WHERE tsm.platform_sys_type = #{sysType} AND tsm.parent_id = 0 AND tsm.is_deleted = 0 ORDER BY tsm.sort")
    List<SysMenu> selectTop(@Param("sysType") Integer sysType);

    /**
     * 根据userId获取所有一级节点,过滤权限
     * @param userId
     * @return
     */
    @Select("SELECT tsm.* FROM t_sys_menu tsm LEFT JOIN t_sys_permission tsp ON tsm.id = tsp.menu_id " +
            "WHERE tsp.user_id = #{userId} AND tsm.platform_sys_type = #{sysType} AND tsm.parent_id = 0 AND tsm.is_deleted = 0 ORDER BY tsm.sort")
    List<SysMenu> selectTopByPermission(@Param("sysType") Integer sysType, @Param("userId") long userId);

    /**
     * 根据parentId获取所有子节点(菜单,页面,按钮),不过滤status,不过滤权限
     * @param parentId
     * @return
     */
    @Select("SELECT * FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.status, t1.sort, t1.api_url, t1.menu_type, t1.sys_code," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT id, parent_id, title, STATUS, sort, api_url, menu_type, sys_code FROM t_sys_menu t WHERE t.is_deleted = 0 ORDER BY parent_id, id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<SysMenu> selectChildren(@Param("parentId") long parentId);

    /**
     * 根据parentId获取所有子节点(菜单,页面),过滤menu_type <> 3,不过滤status,过滤权限
     * @param parentId
     * @return
     */
    @Select("SELECT * FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.status, t1.sort, t1.api_url, t1.menu_type, t1.sys_code," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT tsm.id, tsm.parent_id, tsm.title, tsm.STATUS, tsm.sort, tsm.api_url, tsm.menu_type, tsm.sys_code FROM t_sys_menu tsm LEFT JOIN t_sys_permission tsp ON tsm.id = tsp.menu_id WHERE tsp.user_id = #{userId} AND tsm.menu_type <> 3 AND tsm.is_deleted = 0 ORDER BY tsm.parent_id, tsm.id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<SysMenu> selectChildrenByPermissionAndType(@Param("parentId") long parentId, @Param("userId") long userId);

    /**
     * 根据parentId获取所有子节点(菜单,页面,按钮),不过滤menu_type,不过滤status,过滤权限
     * @param parentId
     * @return
     */
    @Select("SELECT * FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.status, t1.sort, t1.api_url, t1.menu_type, t1.sys_code," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT tsm.id, tsm.parent_id, tsm.title, tsm.STATUS, tsm.sort, tsm.api_url, tsm.menu_type, tsm.sys_code FROM t_sys_menu tsm LEFT JOIN t_sys_permission tsp ON tsm.id = tsp.menu_id WHERE tsp.user_id = #{userId} AND tsm.is_deleted = 0 ORDER BY tsm.parent_id, tsm.id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<SysMenu> selectChildrenByPermission(@Param("parentId") long parentId, @Param("userId") long userId);

    /**
     * 根据parentId获取所有子节点(菜单,页面),过滤menu_type <> 3,过滤status,不过滤权限
     * @param parentId
     * @return
     */
    @Select("SELECT * FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.status, t1.sort, t1.api_url, t1.menu_type, t1.sys_code," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT id, parent_id, title, STATUS, sort, api_url, menu_type, sys_code FROM t_sys_menu t WHERE t.menu_type <> 3 AND t.status = 1 AND t.is_deleted = 0 ORDER BY parent_id, id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<SysMenu> selectChildrenStatus(@Param("parentId") long parentId);

    /**
     * 根据parentId获取所有子节点(菜单,页面,按钮),不过滤menu_type,过滤status,不过滤权限
     * @param parentId
     * @return
     */
    @Select("SELECT * FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.status, t1.sort, t1.api_url, t1.menu_type, t1.sys_code," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT id, parent_id, title, STATUS, sort, api_url, menu_type, sys_code FROM t_sys_menu t WHERE t.status = 1 AND t.is_deleted = 0 ORDER BY parent_id, id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<SysMenu> selectChildrenALLStatus(@Param("parentId") long parentId);

    /**
     * 根据parentId获取所有子节点(菜单,页面),过滤menu_type <> 3,过滤status,过滤权限
     * @param parentId
     * @return
     */
    @Select("SELECT * FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.status, t1.sort, t1.api_url, t1.menu_type, t1.sys_code," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT tsm.id, tsm.parent_id, tsm.title, tsm.STATUS, tsm.sort, tsm.api_url, tsm.menu_type, tsm.sys_code FROM t_sys_menu tsm LEFT JOIN t_sys_permission tsp ON tsm.id = tsp.menu_id WHERE tsp.user_id = #{userId} AND tsm.menu_type <> 3 AND tsm.status = 1 AND tsm.is_deleted = 0 ORDER BY tsm.parent_id, tsm.id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<SysMenu> selectChildrenStatusByPermission(@Param("parentId") long parentId, @Param("userId") long userId);

    /**
     * 根据parentId获取所有子按钮(按钮),过滤menu_type = 3,过滤status,过滤权限
     * @param parentId
     * @return
     */
    @Select("SELECT * FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.status, t1.sort, t1.api_url, t1.menu_type, t1.sys_code," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT tsm.id, tsm.parent_id, tsm.title, tsm.STATUS, tsm.sort, tsm.api_url, tsm.menu_type, tsm.sys_code FROM t_sys_menu tsm LEFT JOIN t_sys_permission tsp ON tsm.id = tsp.menu_id WHERE tsp.user_id = #{userId} AND tsm.menu_type = 3 AND tsm.status = 1 AND tsm.is_deleted = 0 ORDER BY tsm.parent_id, tsm.id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<SysMenu> selectChildrenButtonStatusByPermission(@Param("parentId") long parentId, @Param("userId") long userId);

    /**
     * 根据parentId获取所有子按钮(按钮),过滤menu_type = 3,过滤status,不过滤权限
     * @param parentId
     * @return
     */
    @Select("SELECT * FROM" +
            " (" +
            " SELECT t1.id, t1.parent_id, t1.title, t1.status, t1.sort, t1.api_url, t1.menu_type, t1.sys_code," +
            " IF( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild " +
            " FROM" +
            " ( SELECT tsm.id, tsm.parent_id, tsm.title, tsm.STATUS, tsm.sort, tsm.api_url, tsm.menu_type, tsm.sys_code FROM t_sys_menu tsm WHERE tsm.menu_type = 3 AND tsm.status = 1 AND tsm.is_deleted = 0 ORDER BY tsm.parent_id, tsm.id ) t1," +
            " ( SELECT @pids := #{parentId} ) t2 ) t3" +
            " WHERE" +
            " ischild != 0;")
    List<SysMenu> selectChildrenButtonStatus(@Param("parentId") long parentId);


    /**
     * 根据parentId获取所有子按钮(按钮),过滤menu_type = 3,过滤status,过滤权限
     * @param userId
     * @return
     */
    @Select("SELECT tsm.id, tsm.parent_id, tsm.title, tsm.STATUS, tsm.sort, tsm.api_url, tsm.menu_type, tsm.sys_code FROM t_sys_menu tsm LEFT JOIN t_sys_permission tsp ON tsm.id = tsp.menu_id WHERE tsp.user_id = #{userId} AND tsm.menu_type = 3 AND tsm.status = 1 AND tsm.is_deleted = 0 ")
    List<SysMenuButtonDTO> selectChildrenButtonStatusListByPermission(@Param("userId") long userId);

    /**
     * 根据parentId获取所有子按钮(按钮),过滤menu_type = 3,过滤status,不过滤权限
     * @return
     */
    @Select("select t.id, t.menu_type, t.parent_id, (select tm.menu_type from t_sys_menu tm where tm.id = t.parent_id) parent_menu_type " +
            "from  ( SELECT tsm.id, tsm.menu_type, tsm.parent_id FROM t_sys_menu tsm " +
            "WHERE tsm.menu_type = 3 AND tsm.status = 1 AND tsm.is_deleted = 0) t ")
    List<SysMenuButtonDTO> selectChildrenButtonStatusAll();

    /**
     * 根据menuId获取所有父节点,不过滤权限
     * @param menuId
     * @return
     */
    @Select("SELECT" +
            " T2.id," +
            " T2.title," +
            " T2.parent_id," +
            " T1.LEVEL " +
            "FROM" +
            " (" +
            " SELECT" +
            "  @r AS _id," +
            "  ( SELECT @r := parent_id FROM t_sys_menu WHERE id = _id ) AS parent_id," +
            "  @l := @l + 1 AS LEVEL " +
            " FROM" +
            "  ( SELECT @r := #{menuId}, @l := 0 ) vars," +
            "  t_sys_menu h " +
            " WHERE" +
            "  parent_id IS NOT NULL " +
            " ) T1" +
            " JOIN t_sys_menu T2 ON T1._id = T2.id " +
            "ORDER BY" +
            " T1.LEVEL")
    List<SysMenu> selectParents(@Param("menuId") long menuId);

    /**
     * 根据menuId获取所有父节点id数组,不过滤权限
     * @param menuId
     * @return
     */
    @Select("SELECT" +
            " CONCAT(T2.parent_id) parent_ids " +
            "FROM" +
            " (" +
            " SELECT " +
            "@r AS _id," +
            "( SELECT @r := parent_id FROM t_sys_menu WHERE id = _id ) AS parent_id, " +
            "@l := @l + 1 AS level " +
            " FROM" +
            "( SELECT @r := #{menuId}, @l := 0 ) vars, " +
            "t_sys_menu h " +
            " WHERE" +
            " parent_id IS NOT NULL " +
            " ) T1" +
            " JOIN t_sys_menu T2 ON T1._id = T2.id" +
            " WHERE T2.parent_id <> 0")
    List<Long> selectParentIds(@Param("menuId") long menuId);

    /**
     * 根据userId获取所有菜单节点(菜单,页面),过滤status,过滤权限
     * @param userId
     * @return
     */
    @Select("SELECT tsm.*, tsp.id permissionId FROM t_sys_menu tsm " +
            "LEFT JOIN t_sys_permission tsp ON tsm.id = tsp.menu_id " +
            "WHERE" +
            " tsp.user_id = #{userId}" +
            " AND tsm.platform_sys_type = #{sysType}" +
            " AND tsm.menu_type <> 3" +
            " AND tsm.status = 1" +
            " AND tsm.is_deleted = 0")
    List<SysMenu> selectALLByPermission(@Param("sysType") Integer sysType, @Param("userId") long userId);

}
